﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Configuration.Internal;

namespace SalaryManagementSystem.Models
{
    public class sqlhelper
    {
        //引用Web.config文件中配置的链接字符串
        //public static string SqlCon = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["SalaryManagementBaseEntities1"]);
        //public static string SqlCon = Convert.ToString(ConfigurationManager.ConnectionStrings["connstring"].ConnectionString);
        /// <summary>
        /// 查询所有用户
        /// </summary>
        /// <returns>用户类集合</returns>
        public static string SqlCon = @"data source=.;initial catalog=SalaryManagementBase;persist security info=True;user id=sa;password=sa;MultipleActiveResultSets=True;App=EntityFramework";
        public List<StaffersModel> StafferList()
        {
            using (SqlConnection conn = new SqlConnection(SqlCon))
            {
                SqlCommand cmd = new SqlCommand(); //新建数据库操作类
                cmd.Connection = conn; //设置数据链接
                cmd.CommandText = "SELECT * FROM StafferTable"; //设置查询语句
                if (conn.State != System.Data.ConnectionState.Open)//判断状态
                {
                    conn.Open();//打开数据连接
                }
                SqlDataReader dr = cmd.ExecuteReader(); //执行Reader查询，存入dr

                List<StaffersModel> u1 = new List<StaffersModel>(); //新建一个User类的集合

                while (dr.Read())//遍历dr
                {
                    StaffersModel u0 = new StaffersModel(); //临时User类变量u0

                    //把查询的当前记录各字段值赋值给对于的u0的属性
                    u0.id = int.Parse(dr["id"].ToString());
                    u0.stafferName = dr["stafferName"].ToString();
                    u0.sex = dr["sex"].ToString();
                    u0.job = dr["job"].ToString();
                    u0.getJobTime = DateTime.Parse(dr["getJobTime"].ToString());
                    u0.salary = int.Parse(dr["salary"].ToString());
                    u0.updateTime = DateTime.Parse(dr["updateTime"].ToString());
                    u0.stafferType = dr["stafferType"].ToString();
                    u0.note = dr["note"].ToString();
                    u1.Add(u0);//把有数据的u0加入到User类的集合
                }
                //循环结束后，每一个用户数据都会被加入到用户类集合u1,最后返回u1
                return u1;
            }
        }
    }
}